# coding:utf-8

import os,sys

from openpyxl import Workbook, load_workbook
currentUrl = os.path.dirname(__file__)
parentUrl = os.path.abspath(os.path.join(currentUrl, os.pardir))
sys.path.append(parentUrl)


class SheetIndexError(Exception):
    """Sheet数目下标获取异常"""
    message = "sheet下标超过范围"


class Excel(object):
    """Excel 基类"""

    @staticmethod
    def _load_file(file_name, sheet_name):

        if not os.path.exists(file_name):
            raise FileNotFoundError(
                "file '{file_name}' is not found.".format(file_name=file_name))

        wb = load_workbook(file_name)
        return wb[sheet_name]

    def write_header(self, head: list):
        """生成excel头部信息。

        :param head: excel头部列表
        :type head: list
        :return:
        """
        self.write_row(1, head)

    def write_row(self, row_num, values):
        """写一行。"""
        for column, value in enumerate(values):
            self._cell(row=row_num, column=column + 1, value=value)

    def write_rows(self, row_start, rows):
        """写多行。"""

        row_num = row_start
        for row in rows:
            self.write_row(row_num, row)
            row_num += 1

    def save(self):
        self.save2file(self._file)

    def save2file(self, file_name):
        """保存到指定文件。"""

        self._excel.save(file_name)

    def set_sheet_title(self, title):
        self._current_sheet.title = title

    def _cell(self, row, column, value=None):
        return self._current_sheet.cell(row, column, value)

    def sheet_select(self, index):
        """选取某个sheet作为当前活动sheet"""
        try:
            self._current_sheet = self._excel.get_index(index)
        except IndexError:
            raise SheetIndexError(SheetIndexError.message)

    @property
    def row_number(self):
        """获取当前excel的row。"""

        return self._current_sheet.max_row

    @property
    def column_number(self):
        """获取excel的column。"""

        return self._current_sheet.max_column

    def read_row(self, row):
        """读取第row行的数据。"""
        # iter_rows(self, range_string=None, min_row=None, max_row=None, min_col=None, max_col=None,
        #           row_offset=0, column_offset=0):
        data = self._current_sheet.iter_rows(min_row=row, max_row=row, max_col=self._current_sheet.max_column)
        res = []
        for item in data:
            for i in item:
                res.append(i.value)
        return res

    def read_column(self, col):
        """读取第column列的数据。"""
        # iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None):
        data = self._current_sheet.iter_cols(min_col=col, max_col=col)
        res = []
        for item in data:
            for i in item:
                res.append(i.value)
        return res

    def read_excel2dict(self):
        pass

    def readtestcase2dict(self, file_name, sheet_name):
        excel = ExcelReader(file_name, sheet_name)
        row_num = excel.row_number
        header = excel.read_row(1)
        arrays = []
        for row in range(2, row_num + 1):
            excel_dict = {}
            for key in range(len(header)):
                excel_dict[header[key]] = excel.read_row(row)[key]
            arrays.append(excel_dict)
        return arrays
        

class ExcelReader(Excel):

    def __init__(self, file_name, sheet_name):
        self._file = file_name
        self._excel = self._load_file(self._file, sheet_name)
        self._current_sheet = self._excel


class ExcelWriter(Excel):
    def __init__(self, file_name):
        self._file = file_name
        self._excel = Workbook()
        self._current_sheet = self._excel.active

if __name__ == '__main__':
    filepath="G:\\Gitee\\ApiTestTools_Python\\testcases\\template.xlsx"
    a = ExcelReader(filepath,'testcases')
    b = a.readtestcase2dict(filepath,'testcases')
    print(b)